package com.vonzhou.spitter.persistence;

import org.joda.time.DateTime;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class JdbcSpitterDao extends SimpleJdbcDaoSupport implements SpitterDao {

	private static final String SQL_INSERT_SPITTER =
			"insert into spitter (username, password, fullname) " +
					"values (?, ?, ?)";

	private static final String SQL_UPDATE_SPITTER =
			"update spitter set username = ?, password = ?, fullname = ?"
					+ "where id = ?";

	private static final String SQL_SELECT_SPITTER =
			"select id, username, password, fullname from spitter";

	private static final String SQL_SELECT_SPITTER_BY_ID =
			SQL_SELECT_SPITTER + " where id=?";

	private static final String SQL_INSERT_SPITTLE = "" +
			"insert into spittle (spitter_id, spittleText, postedTime) values (?, ?, ?)";

	private static final String SQL_SELECT_SPITTLE =
			"select id, spitter_id, spittleText, postedTime from spittle";

	private static final String SQL_SELECT_RECENT_SPITTLE =
			SQL_SELECT_SPITTLE + " where postedTime > ? order by postedTime desc";

	@Override
	public Spitter getSpitterById(long id) {
		return getSimpleJdbcTemplate().queryForObject(
				SQL_SELECT_SPITTER_BY_ID,
				new ParameterizedRowMapper<Spitter>() {
					@Override
					public Spitter mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Spitter spitter = new Spitter();
						spitter.setId(rs.getLong(1));
						spitter.setUsername(rs.getString(2));
						spitter.setPassword(rs.getString(3));
						spitter.setFullName(rs.getString(4));
						return spitter;
					}
				}, id);
	}

	@Override
	public void addSpitter(Spitter spitter) {
		getSimpleJdbcTemplate().update(
				SQL_INSERT_SPITTER,
				new Object[]{spitter.getUsername(),
						spitter.getPassword(),
						spitter.getFullName()});
//        spitter.setId(queryForIdentity());
	}

	@Override
	public void saveSpitter(Spitter spitter) {
		getSimpleJdbcTemplate().update(
				SQL_UPDATE_SPITTER,
				new Object[]{spitter.getUsername(), spitter.getPassword(),
						spitter.getFullName(), spitter.getId()});
	}

	@Override
	public void saveSpittle(Spittle spittle) {
		getSimpleJdbcTemplate().update(SQL_INSERT_SPITTLE, new Object[]{
				spittle.getSpitter().getId(),
				spittle.getText(),
				new Date()
		});
	}

	@Override
	public List<Spittle> getRecentSpittle() {
		DateTime dt = new DateTime().minusDays(1);

		return getSimpleJdbcTemplate().query(SQL_SELECT_RECENT_SPITTLE,
				new ParameterizedRowMapper<Spittle>() {
					@Override
					public Spittle mapRow(ResultSet rs, int rowNum) throws SQLException {
						Spittle spittle = new Spittle();

						spittle.setId(rs.getLong(1));
						spittle.setSpitter(getSpitterById(rs.getLong(2)));
						spittle.setText(rs.getString(3));
						spittle.setWhen(rs.getDate(4));

						return spittle;
					}
				}, dt.toDate());
	}

	// 后面的SQL语法不对(MySQL)
	private long queryForIdentity() {
		return getJdbcTemplate().queryForLong("call identity()");
	}

	@Override
	public List<Spittle> getSpittlesForSpitter(
			Spitter spitter) {
		// TODO Auto-generated method stub
		return null;
	}


	@Override
	public Spitter getSpitterByUsername(String username) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void deleteSpittle(long id) {
		// TODO Auto-generated method stub
		throw new UnsupportedOperationException();
	}

	@Override
	public Spittle getSpittleById(long id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Spitter> findAllSpitters() {
		// TODO Auto-generated method stub
		return null;
	}
}
